<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/7/31 0031
 * Time: 上午 10:50
 */

namespace app\admin\controller;
header('Access-Control-Allow-Origin:*');
header('Access-Control-Allow-Methods:POST,GET');
header('Access-Control-Allow-Headers:x-requested-with,content-type');
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Controller;
use think\Db;
define('DS', DIRECTORY_SEPARATOR);
defined('APP_PATH') or define('APP_PATH', dirname($_SERVER['SCRIPT_FILENAME']) . DS);
defined('ROOT_PATH') or define('ROOT_PATH', dirname(realpath(APP_PATH)) . DS);

/**
 * 申报表
 * Class Declares
 * @package app\admin\controller
 * 关联数据表 hn_declare
 */
class Declares extends Authinfo
{
    //申报表列表页
    public function declare_index(){
        $uid=input('uid');
        $map=array();
        //自定义超级管理员id为10
        if($uid!=10){
            $company_id=Db::name('Member')->where(['id'=>$uid])->value('company_id');
            $company_name=Db::name('Department')->where(['id'=>$company_id])->value('name');
            $map[]=array('company','=',$company_name);
        }else{
            //接收传过来的单位id得到单位名称
            $company_name=input('company');
            if($company_name){
                $map[]=array('company','=',$company_name);
            }
        }

        //模糊查询 证件号及
        $cat_name=input('cat_name');
        if($cat_name){
            $map[]=array('cat_name','like','%'.$cat_name.'%');
        }
        //干部姓名
        $g_name=input('g_name');
        if($g_name){
            $map[]=array('g_name','like','%'.$g_name.'%');
        }
        //身份证
        $sfid=input('sfid');
        if($sfid){
            $map[]=array('sfid','=',$sfid);
        }
        // 分页
        $current=input('current');
        if(!$current){
            $current=1;
        }
        $pagenums=input('pagenums');
        if(!$pagenums){
            $pagenums=50;
        }
        $nowpagenums=($current-1)*$pagenums;
        $order=input('order');
        if(!$order){
            $order='id DESC';
        }

        if($map){

            $total=Db::name('Declare')
                ->where($map)
                ->group('cat_name')
                ->group('g_name')
                ->count();
            $info=Db::name('Declare')
                ->where($map)
                ->group('cat_name')
                ->group('g_name')
                ->order($order)
                ->limit($nowpagenums,$pagenums)
                ->select();

            $amount_count=Db::name('Declare')
                ->where($map)
                ->sum('price');
            $people_nums=Db::name('Declare')
                ->where($map)
                ->count();
        }else{
            $total=Db::name('Declare')
                ->group('cat_name')
                ->group('g_name')
                ->count('id');
            $info=Db::name('Declare')
                ->field('hb_declare.*')
                ->group('cat_name')
                ->group('g_name')
                ->order($order)
                ->limit($nowpagenums,$pagenums)
                ->select();

            //列表头部的资金总额和人数总数
            $amount_count=Db::name('Declare')
                ->sum('price');
            $people_nums=Db::name('Declare')
                ->count();

        }

        foreach($info as $k=>$v){
            //列表数据红的资金人数数量
            $info[$k]['nums']=Db::name('Declare')
                ->where('company',$v['company'])
                ->where('cat_name',$v['cat_name'])
                ->count($v['sfid']);
            $info[$k]['count']=Db::name('Declare')
                ->where('g_name',$v['g_name'])
                ->where('cat_name',$v['cat_name'])

                ->count($v['sfid']);
        }

        if($info){
            $datas['status']=1;
            $datas['info']=$info;
            $datas['total']=$total;
            $datas['amount_count']=$amount_count;
            $datas['people_nums']=$people_nums;
            $datas['current']=$current;
        }else{
            $datas['status']=0;
            $datas['info']='无数据！';
            $datas['total']=$total;
            $datas['amount_count']=$amount_count;
            $datas['people_nums']=$people_nums;
            $datas['current']=$current;
        }
        return json($datas);
    }

    //申报表新增数据
    public function declare_add(){
        $g_name=input('g_name');
        if($g_name){
            $info['g_name']=$g_name;
        }
        $sfid=input('sfid');
        if($sfid){
            $info['sfid']=$sfid;
        }
        $s_name=input('s_name');
        if($s_name){
            $info['s_name']=$s_name;
        }
        $q_sfid=input('q_sfid');
        if($q_sfid){
            $info['q_sfid']=$q_sfid;
        }
        $relationship=input('relationship');
        if($relationship){
            $info['relationship']=$relationship;
        }
        $is_poor=input('is_poor');
        if($is_poor){
            $info['is_poor']=$is_poor;
        }
        $is_households=input('is_households');
        if($is_households){
            $info['is_households']=$is_households;
        }
        $basis=input('basis');
        if($basis){
            $info['basis']=$basis;
        }
        $content=input('content');
        if($content){
            $info['content']=$content;
        }
        $cat_name=input('cat_name');
        if($cat_name){
            $info['cat_name']=$cat_name;
        }else{
            exit;
        }
        $company=input('company');
        if($company){
            $info['company']=$company;
        }
        $price=input('price');
        if($price){
            $info['price']=$price;
        }
        $release_time=input('release_time');
        if($release_time){
            $info['release_time']=strtotime($release_time);
        }
        $address=input('address');
        if($address){
            $info['address']=$address;
        }
        $info['create_time']=time();
        $result=Db::name('Declare')
            ->where(['sfid'=>$sfid])
            ->where(['q_sfid'=>$q_sfid])
            ->where(['cat_name'=>$cat_name])
            ->find();
        if(!$result){
            if($info){
                $result=Db::name('Declare')->insert($info);
                if($result){
                    $datas['status']=1;
                    $datas['info']='新增成功！';
                }else{
                    $datas['status']=0;
                    $datas['info']='新增失败!';
                }
            }else{
                $datas['status']=0;
                $datas['info']='参数缺失!';
            }
        }else{
            $datas['status']=0;
            $datas['info']='数据已上报!';
        }

        return json($datas);

    }
    //查看
    public function declare_view(){
        $map=[];
        $id=input('id');
        $s_name=input('name');
        if($s_name){
            $map[]=array('s_name','like','%'.$s_name.'%');
        }
        $address=input('address');
        if($address){
            $map[]=array('address','like','%'.$address.'%');
        }
        // 分页
        $current=input('current');
        if(!$current){
            $current=1;
        }
        $pagenums=input('pagenums');
        if(!$pagenums){
            $pagenums=50;
        }
        $nowpagenums=($current-1)*$pagenums;
        $order=input('order');
        if(!$order){
            $order='id DESC';
        }
        if($id){
            $row=Db::name('Declare')->where(['id'=>$id])->find();
            if($map){
                $total=Db::name('Declare')
                    ->where($map)
                    ->where(['cat_name'=>$row['cat_name']])
                    ->where(['sfid'=>$row['sfid']])
                    ->count();
                $info=Db::name('Declare')
                    ->where($map)
                    ->where(['cat_name'=>$row['cat_name']])
                    ->where(['sfid'=>$row['sfid']])
                    ->order($order)
                    ->limit($nowpagenums,$pagenums)
                    ->select();
                $people_num=Db::name('Declare')
                    ->where($map)
                    ->where(['sfid'=>$row['sfid']])
                    ->where(['cat_name'=>$row['cat_name']])
                    ->count();
                $amount=Db::name('Declare')
                    ->where($map)
                    ->where(['sfid'=>$row['sfid']])
                    ->where(['cat_name'=>$row['cat_name']])
                    ->sum('price');
            }else{
                $total=Db::name('Declare')
                    ->where(['cat_name'=>$row['cat_name']])
                    ->where(['sfid'=>$row['sfid']])
                    ->count();
                $info=Db::name('Declare')
                    ->where(['cat_name'=>$row['cat_name']])
                    ->where(['sfid'=>$row['sfid']])
                    ->order($order)
                    ->limit($nowpagenums,$pagenums)
                    ->select();
                $people_num=Db::name('Declare')
                    ->where(['sfid'=>$row['sfid']])
                    ->where(['cat_name'=>$row['cat_name']])
                    ->count();
                $amount=Db::name('Declare')
                    ->where(['sfid'=>$row['sfid']])
                    ->where(['cat_name'=>$row['cat_name']])
                    ->sum('price');
            }

            if($info){
                $datas['status']=1;
                $datas['total']=$total;
                $datas['people_num']=$people_num;
                $datas['amount']=$amount;
                $datas['info']=$info;
            }else{
                $datas['status']=0;
                $datas['info']='无数据！';
            }
        }else{
            $datas['status']=0;
            $datas['info']='无参数！';
        }
        return json($datas);

    }

    //修改数据回显
    public function update_list(){
        $id=input('id');
        $list=Db::name('Declare')->where(['id'=>$id])->find();
        if($list){
            $datas['status']=1;
            $datas['info']=$list;
        }else{
            $datas['status']=0;
            $datas['info']='无数据!';
        }
        return json($datas);
    }
    //查看页修改
    public function update_view(){
        $id=input('id');
            if($id){
                $g_name=input('g_name');
                if($g_name){
                    $info['g_name']=$g_name;
                }
                $sfid=input('sfid');
                if($sfid){
                    $info['sfid']=$sfid;
                }
                $s_name=input('s_name');
                if($s_name){
                    $info['s_name']=$s_name;
                }
                $q_sfid=input('q_sfid');
                if($q_sfid){
                    $info['q_sfid']=$q_sfid;
                }
                $relationship=input('relationship');
                if($relationship){
                    $info['relationship']=$relationship;
                }
                $is_poor=input('is_poor');
                if($is_poor){
                    $info['is_poor']=$is_poor;
                }
                $is_households=input('is_households');
                if($is_households){
                    $info['is_households']=$is_households;
                }
                $basis=input('basis');
                if($basis){
                    $info['basis']=$basis;
                }
                $content=input('content');
                if($content){
                    $info['content']=$content;
                }
                $cat_name=input('cat_name');
                if($cat_name){
                    $info['cat_name']=$cat_name;
                }
                $company=input('company');
                if($company){
                    $info['company']=$company;
                }
                $price=input('price');
                if($price){
                    $info['price']=$price;
                }
                $release_time=input('release_time');
                if($release_time){
                    $info['release_time']=strtotime($release_time);
                }
                $address=input('address');
                if($address){
                    $info['address']=$address;
                }
                if($info){
                    $list=Db::name('Declare')->where(['id'=>$id])->update($info);
                    if($list){
                        $datas['status']=1;
                        $datas['info']='操作成功';
                    }else{
                        $datas['status']=0;
                        $datas['info']='修改失败!';
                    }
                }else{
                    $datas['status']=0;
                    $datas['info']='无数据!';
                }
            }else{
                $datas['status']=0;
                $datas['info']='参数缺失!';
            }

        return json($datas);
    }
    //申报表批量删除
    public function declare_delete(){
        $ids=input('id');
        if(is_array($ids)){
            $info=Db::name('Declare')->where('id','in',$ids)->delete();
            if($info){
                $datas['status']=1;
                $datas['info']='删除成功！';
            }else{
                $datas['status']=0;
                $datas['info']='删除失败！';
            }
        }else{
            $datas['status']=0;
            $datas['info']='无参数！';
        }
        return json($datas);
    }

    //查看页批量删除
    public function delete_view(){
        $idArray=input('id');
        if($idArray){
            $result = Db::name('Declare')
                ->where('id', 'in', $idArray)
                ->delete();
            if($result){
                $datas['status'] = 1;
                $datas['info'] = '删除成功';
            }else{
                $datas['status'] = 0;
                $datas['info'] = '删除失败！';
            }
        }else{
            $datas['status']=0;
            $datas['info']='无参数！';
        }
        return json($datas);
    }
    //批量导入
    public function pladd(){
        $pathurl=input('pathurl');
//                $pathurl='uploads\wenjian\20180619\20180802.xlsx';
        $startRow=input('startRow');
        if(!$startRow){
            $startRow=2;
        }
        if($pathurl){
            $lujing=$pathurl;
            $excelFile=ROOT_PATH.$lujing;
            $excelReader =IOFactory::createReader('Xlsx');
            $excelReader->setReadDataOnly(true);
            $endRow = input('endRow');
            if (!$endRow) {
                $endRow = 2000;
            }
            $filterSubset=new Ireadefilter($startRow, $endRow,range('A', 'N'));
            $excelReader->setReadFilter($filterSubset);
            $phpexcel    = $excelReader->load($excelFile);
            $activeSheet = $phpexcel->getActiveSheet();
            $zongshu=$activeSheet->getHighestRow(); //总行数
            if($endRow>$zongshu){
                $endRow=$zongshu;
            }
            if($startRow<=$zongshu){
                $sheetData = $activeSheet->toArray(null, true, true, true);
                $highestColumn      = $activeSheet->getHighestColumn(); //最后列数所对应的字母，例如第2行就是B
                $data = array();
                for ($col = $startRow; $col <=$endRow; $col++) {
                    if($sheetData[$col]){
                        $data[] = $sheetData[$col];
                    }
                }
                $phpexcel->disconnectWorksheets();
                unset($phpexcel);
                $shujudata=array();
                if($data){
                    $nums=0;
                    foreach ($data as $key => $value){
                        $shujudata['g_name']=$value['A'];
                        $shujudata['sfid']=$this->NumToStr($value['B']);
                        $shujudata['relationship']=$value['C'];
                        $shujudata['s_name']=$value['D'];
                        $shujudata['q_sfid']=$this->NumToStr($value['E']);
                        $shujudata['is_poor']=$value['F'];
                        $shujudata['is_households']=$value['G'];
                        $shujudata['cat_name']=$value['H'];
                        $shujudata['basis']=$value['I'];
                        $shujudata['address']=$value['J'];
                        $shujudata['content']=$value['K'];
                        $shujudata['price']=$value['L'];
                        $shujudata['company']=$value['M'];
                        $shujudata['release_time']=$value['N'];

                        if($value['B']){//干部sfid存在
                            //查询是否存在数据库中
                            $cz=Db::name('Declare')->where('sfid',$value['B'])->find();
                            if(!$cz){
                                //不存在则进行添加操作
                                $shujudata['create_time']=time();
                                $idss=Db::name('Declare')->insert($shujudata);
                                if($idss){
                                    $nums++;
                                    unset($shujudata);
                                }
                                //判断是干部id且亲属关系不是本人===》添加亲属
                            }else if($value['B']&&$value['C']!='本人'){
                                    //查看是否存在亲属信息
                                    $info=Db::name('Declare')
                                        ->where('sfid',$value['F'])
                                        ->where('relationship',$value['C'])
                                        ->where('cat_name',$value['H'])
                                        ->find();
                                    if(!$info){//不存在则进行添加操作
                                        $shujudata['create_time']=time();
                                        $idss=Db::name('Declare')->insert($shujudata);
                                        if($idss){
                                            $nums++;
                                            unset($shujudata);
                                        }
                                    }else{//存在则进行重复数据统一操作
                                        $shujudata['create_time']=time();
                                        $gx=Db::name('Declare')->where('id',$info['id'])->update($shujudata);
                                        $nums++;
                                        unset($shujudata);
                                    }
                            }else if($value['B']&&$value['H']&&$value['C']!='本人'){
                                //如果sfid相同 资金项目相同
                                $info2=Db::name('Declare')
                                    ->where('sfid',$value['B'])
                                    ->where('cat_name',$value['H'])
                                    ->where('relationship','<>','本人')
                                    ->find();
                                if(!$info2){//不存在则进行添加操作
                                    $shujudata['create_time']=time();
                                    $idss=Db::name('Declare')->insert($shujudata);
                                    if($idss){
                                        $nums++;
                                        unset($shujudata);
                                    }
                                }else{//存在则进行重复数据统一操作
                                    $shujudata['create_time']=time();
                                    $gx=Db::name('Declare')->where('id',$info2['id'])->update($shujudata);
                                    $nums++;
                                    unset($shujudata);
                                }
                            }else{
                                $shujudata['create_time']=time();
                                $gx=Db::name('Declare')->where('id',$cz['id'])->update($shujudata);
                                $nums++;
                                unset($shujudata);
                            }
                            unset($cz);
                        }
                    }
                    $datas['status']=1;
                    $tiaoshu=$endRow-2;
                    $datas['cginfo']=$nums;
                    $datas['nextnums']=$endRow+1;
                    $datas['isend']=0;
                    unset($data);
                }else{
                    $datas['status']=0;
                    $datas['info']='无数据！';
                }
            }else{
                $datas['status']=1;
                $datas['info']='导入完毕';
                $datas['isend']=1;
            }
        }else{
            $datas['status']=0;
            $datas['info']='文件不存在';
        }
        return  json($datas);

    }

    //将科学计数法转换为原始数字字符串的方法
    function NumToStr($num){
        if (stripos($num,'e')===false) return $num;
        $num = trim(preg_replace('/[=\'"]/','',$num,1),'"');//出现科学计数法，还原成字符串
        $result = "";
        while ($num > 0){
            $v = $num - floor($num / 10)*10;
            $num = floor($num / 10);
            $result = $v . $result;
        }
        return $result;
    }

    //php 多维数组去重
    function super_unique($array, $recursion = false){
        // 序列化数组元素,去除重复
        $result = array_map('unserialize', array_unique(array_map('serialize', $array)));
        // 递归调用
        if ($recursion) {
            foreach ($result as $key => $value) {
                if (is_array($value)) {
                    $result[ $key ] = super_unique($value);
                }
            }
        }
        return $result;
    }

    //左侧分类列表
    public function index_department(){
        $uid=input('uid');

        //通过用户的id得到此用户的部门
        $map=[];
        //自定义超级管理员id为10
        if($uid!=10){
            $company_id=Db::name('Member')->where(['id'=>$uid])->value('company_id');
            $map[]=array('id','=',$company_id);
        }
        $order = input('order');
        if($order){
            $order='id DESC';
        }// 排序
        if($map){
            $infos=Db::name('Department')
                ->field('hb_cat.name,hb_department.name')
                ->join('hb_cat','hb_department.name=hb_cat.department_id')
                ->where($map)
                ->order($order)
                ->select();
        }else{
            $infos=Db::name('Department')
                ->field('hb_cat.name,hb_department.name')
                ->join('hb_cat','hb_department.name=hb_cat.department_id')
                ->order($order)
                ->select();
        }
        foreach($infos as $k => $v){

            $infos[$k]['son']=Db::name('Cat')->field('department_id,name')->where(['department_id'=>$v['name']])->select();
        }
        if($infos){
            $datas['status']=1;
            $datas['info']=$this->super_unique($infos);
        }else{
            $datas['status']=0;
            $datas['info']='无数据！';
        }

        return json($datas);
    }

    //项目数据
    public function cat_index(){
        $order = input('order');
        if($order){
            $order='id DESC';
        }// 排序
        $infos=Db::name('Cat')->where('pid',0)->field('id,pid,name')->order($order)->select();
        foreach($infos as $k => $v){
            $infos[$k]['son']=Db::name('Cat')->where('pid',$v['id'])->field('id,pid,name')->order($order)->select();
        }
        if($infos){
            $datas['status']=1;
            $datas['info']=$infos;
        }else{
            $datas['status']=0;
            $datas['info']='无数据！';
        }
        return json($datas);
    }

    //新增中的发布单位
    public function index_company(){
        $infos=Db::name('Department')->where('pid',0)->field('id,pid,name')->select();
        foreach($infos as $k => $v){
            $infos[$k]['son']=Db::name('Department')->where('pid',$v['id'])->field('id,pid,name')->select();
        }

        if($infos){
            $datas['status']=1;
            $datas['info']=$infos;
        }else{
            $datas['status']=0;
            $datas['info']='无数据！';
        }
        return json($datas);
    }
}